package com.ting.encrypt.utils;

import com.alibaba.fastjson.JSON;
import com.ting.encrypt.entity.FieldAndIndexEntity;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.BinaryExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.JdbcParameter;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.operators.relational.*;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.update.UpdateSet;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.apache.commons.collections.CollectionUtils;
import java.io.StringReader;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * 基于Jsqlparser的sql解析功能，并获取表名和where后面的条件
 */
@Slf4j
public class JsqlparserUtil {
  /**
   * 获取SQL中的全部表名
   *
   * @param sql
   * @return
   */
  public static List<String> getTableName(String sql) {
    List<String> names = new ArrayList<>();
    try {
      Statement statement = CCJSqlParserUtil.parse(sql);
      TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
      names = tablesNamesFinder.getTableList(statement);
    } catch (JSQLParserException e) {
      log.error("getTableName error", e);
    }
    return names;
  }
  /**
   * 获取SQL中要设置值的字段名称及对应?占位符的位置
   *
   * @param sql
   * @param isOnlyWhere 是否包含非where条件的内容
   * @return
   * @throws JSQLParserException
   */
  public static List<FieldAndIndexEntity> getCloumnNames(String sql, boolean isOnlyWhere)
      throws JSQLParserException {
    // 装载where后面的字段名称
    List<FieldAndIndexEntity> fieldAndIndexEntities = new ArrayList<>();
    StringBuffer stringBuffer = new StringBuffer();
    stringBuffer.append(sql);
    Statement statement = CCJSqlParserUtil.parse(new StringReader(stringBuffer.toString()));
    if (statement instanceof Select) { // 针对查询操作
      Select istatement = (Select) statement;
      Expression where = ((PlainSelect) istatement.getSelectBody()).getWhere();
      if (null != where) {
        fieldAndIndexEntities = getParser(where, fieldAndIndexEntities);
      }
    } else if (statement instanceof Insert) { // 针对插入操作
      Insert insert = (Insert) statement;
      List<Column> columns = insert.getColumns();
      ItemsList itemsList = insert.getItemsList();
      getUpdateColumn(fieldAndIndexEntities, columns, itemsList);
    } else if (statement instanceof Update) { // 针对更新操作
      Update update = (Update) statement;

      ArrayList<UpdateSet> updateSets = update.getUpdateSets();
      if (!isOnlyWhere) {
        for (int i = 0; i < updateSets.size(); i++) {
          UpdateSet updateSet = updateSets.get(i);
          Column column = updateSet.getColumns().get(0);
          FieldAndIndexEntity fieldAndIndexEntity = new FieldAndIndexEntity();
          fieldAndIndexEntity.setFieldName(column.getColumnName());
          fieldAndIndexEntity.setIndex(Arrays.asList(i + 1));
          fieldAndIndexEntities.add(fieldAndIndexEntity);
        }
      }
      Expression where = update.getWhere();
      if (null != where) {
        fieldAndIndexEntities = getParser(where, fieldAndIndexEntities);
      }
      // 如果只获取where条件中的字段及?占位符位置的信息，则要去除其余的数据
      if (isOnlyWhere && CollectionUtils.isNotEmpty(fieldAndIndexEntities)) {
        int disSize = updateSets.size();
        for (FieldAndIndexEntity fieldAndIndexEntity : fieldAndIndexEntities) {
          List<Integer> indexs = fieldAndIndexEntity.getIndex();
          for (int i = 0; i < indexs.size(); i++) {
            indexs.set(i, indexs.get(i) - disSize);
          }
        }
      }
    }
    return fieldAndIndexEntities;
  }
  /**
   * 获取更新的字段（insert，update）
   *
   * @param fieldAndIndexEntities
   * @param columns
   * @param itemsList
   */
  private static void getUpdateColumn(
      List<FieldAndIndexEntity> fieldAndIndexEntities, List<Column> columns, ItemsList itemsList) {
    if (itemsList instanceof ExpressionList) { // 单条插入
      for (int i = 0; i < columns.size(); i++) {
        Column column = columns.get(i);
        FieldAndIndexEntity fieldAndIndexEntity = new FieldAndIndexEntity();
        fieldAndIndexEntity.setFieldName(column.getColumnName());
        fieldAndIndexEntity.setIndex(Arrays.asList(i + 1));
        fieldAndIndexEntities.add(fieldAndIndexEntity);
      }
    } else if (itemsList instanceof MultiExpressionList) { // 批量插入
      MultiExpressionList multiExpressionList = (MultiExpressionList) itemsList;
      List<ExpressionList> exprList = multiExpressionList.getExpressionLists();
      for (int i = 0; i < columns.size(); i++) {
        Column column = columns.get(i);
        FieldAndIndexEntity fieldAndIndexEntity = new FieldAndIndexEntity();
        fieldAndIndexEntity.setFieldName(column.getColumnName());
        List<Integer> indexList = new ArrayList<>();
        for (int i1 = 0; i1 < exprList.size(); i1++) {
          ExpressionList expressionList = exprList.get(i1);
          List<Expression> expressions = expressionList.getExpressions();
          Expression expression = expressions.get(i);
          if (expression instanceof JdbcParameter) {
            indexList.add(((JdbcParameter) expression).getIndex());
          }
        }
        fieldAndIndexEntity.setIndex(indexList);
        fieldAndIndexEntities.add(fieldAndIndexEntity);
      }
    }
  }
  private static List<FieldAndIndexEntity> getParser(
      Expression expression, List<FieldAndIndexEntity> fieldAndIndexEntities) {
    // 初始化接受获得的字段信息
    if (expression instanceof BinaryExpression) {
      // 获得左边表达式
      Expression leftExpression = ((BinaryExpression) expression).getLeftExpression();
      FieldAndIndexEntity fieldAndIndexEntity = new FieldAndIndexEntity();
      // 获得左边表达式为Column对象，则直接获得列名
      if (leftExpression instanceof Column) {
        fieldAndIndexEntity.setFieldName(((Column) leftExpression).getColumnName());
      } else if (leftExpression instanceof InExpression) {
        parserInExpression(leftExpression, fieldAndIndexEntities);
      } else if (leftExpression instanceof BinaryExpression) { // 递归调用
        getParser(leftExpression, fieldAndIndexEntities);
      } else if (leftExpression instanceof Parenthesis) { // 递归调用
        Expression expression1 = ((Parenthesis) leftExpression).getExpression();
        getParser(expression1, fieldAndIndexEntities);
      }
      // 获得右边表达式，并分解
      Expression rightExpression = ((BinaryExpression) expression).getRightExpression();
      if (rightExpression instanceof JdbcParameter) {
        fieldAndIndexEntity.setIndex(Arrays.asList(((JdbcParameter) rightExpression).getIndex()));
        fieldAndIndexEntities.add(fieldAndIndexEntity);
      } else if (rightExpression instanceof BinaryExpression) {
        getParser(rightExpression, fieldAndIndexEntities);
      } else if (rightExpression instanceof InExpression) {
        parserInExpression(rightExpression, fieldAndIndexEntities);
      } else if (rightExpression instanceof Parenthesis) { // 递归调用
        Expression expression1 = ((Parenthesis) rightExpression).getExpression();
        getParser(expression1, fieldAndIndexEntities);
      }
    } else if (expression instanceof InExpression) {
      parserInExpression(expression, fieldAndIndexEntities);
    } else if (expression instanceof Between) {
      parserBetweenExpression(expression, fieldAndIndexEntities);
    } else if (expression instanceof Parenthesis) { // 递归调用
      Expression expression1 = ((Parenthesis) expression).getExpression();
      getParser(expression1, fieldAndIndexEntities);
    }
    return fieldAndIndexEntities;
  }
  /**
   * 解析between查询
   *
   * @param expression
   * @param fieldAndIndexEntities
   */
  private static void parserBetweenExpression(
      Expression expression, List<FieldAndIndexEntity> fieldAndIndexEntities) {
    Between between = (Between) expression;
    Expression leftExpression = between.getLeftExpression();
    FieldAndIndexEntity fieldAndIndexEntity = new FieldAndIndexEntity();
    if (leftExpression instanceof Column) {
      fieldAndIndexEntity.setFieldName(((Column) leftExpression).getColumnName());
    }
    List<Integer> indexs = new ArrayList<>();
    Expression betweenExpressionStart = between.getBetweenExpressionStart();
    if (betweenExpressionStart instanceof JdbcParameter) {
      indexs.add(((JdbcParameter) betweenExpressionStart).getIndex());
    }
    Expression betweenExpressionEnd = between.getBetweenExpressionEnd();
    if (betweenExpressionEnd instanceof JdbcParameter) {
      indexs.add(((JdbcParameter) betweenExpressionEnd).getIndex());
    }
    if (CollectionUtils.isNotEmpty(indexs)) {
      fieldAndIndexEntity.setIndex(indexs);
    }
    fieldAndIndexEntities.add(fieldAndIndexEntity);
  }
  /**
   * 解析in关键字左边的条件
   *
   * @param expression
   */
  private static void parserInExpression(
      Expression expression, List<FieldAndIndexEntity> fieldAndIndexEntities) {
    InExpression inExpression = (InExpression) expression;
    Expression leftExpression = inExpression.getLeftExpression();
    ExpressionList rightItemsList = (ExpressionList) inExpression.getRightItemsList();
    FieldAndIndexEntity fieldAndIndexEntity = new FieldAndIndexEntity();
    if (leftExpression instanceof Column) {
      fieldAndIndexEntity.setFieldName(((Column) leftExpression).getColumnName());
    }
    List<Integer> indexs = new ArrayList<>();
    rightItemsList
        .getExpressions()
        .forEach(
            expression1 -> {
              if (expression1 instanceof JdbcParameter) {
                JdbcParameter jdbcParameter = (JdbcParameter) expression1;
                indexs.add(jdbcParameter.getIndex());
              }
            });
    if (CollectionUtils.isNotEmpty(indexs)) {
      fieldAndIndexEntity.setIndex(indexs);
      fieldAndIndexEntities.add(fieldAndIndexEntity);
    }
  }
  /**
   * 测试类
   *
   * @param args
   * @throws JSQLParserException
   */
  public static void main(String[] args) throws JSQLParserException {
    String sql =
        "update user set\n"
            + "          \n"
            + "            json_str = ?\n"
            + "         , \n"
            + "            user_name = ?\n"
            + "         , \n"
            + "            idcard = ?\n"
            + "         \n"
            + "        where id=?";
    sql="insert into a(id,name,age) value(?,?,?),(?,?,?)";
    List<FieldAndIndexEntity> cloumnNames = getCloumnNames(sql, false);
    System.out.println(JSON.toJSONString(cloumnNames));
/*    List<String> tableName = getTableName(sql);
    System.out.println("tableName:" + JSON.toJSONString(tableName));
    if (null != tableName) {
      JsqlparserUtil jsqlparserUtil = new JsqlparserUtil();
      System.out.println(
          "cloumnNames:" + JSON.toJSONString(jsqlparserUtil.getCloumnNames(sql, true)));
    }*/
  }
}